using System;
using System.Collections.Generic;
using System.Text;
using SQLGen;

namespace SQLGenerationTest
{
    class TestSelect
    {

        public static bool PerformTest()
        {
            string sql = "";
            RunTest[] tests = { SimpleSelect1, SimpleSelect2, FullSelect, GroupSelect, HavingSelect, BaseConstructorSelect, AndReductionSelect, OrReductionSelect };
            foreach (RunTest test in tests)
            {
                sql = test();
                Console.WriteLine(sql + "\n");
                Log.Write(sql + "\n");
            }

            return true;
        }

        static string SimpleSelect1()
        {
            Statement stmt = Statement.SimpleSel(
                new string[] { "foo", "bar", "baz" },                                                       // fields
                From.TableName("mytable").InnerJoin("anothertable", "joinfield", "joinfield"),              // from/join
                Where.All);                                                                                 // where clause

            return SqlModel.emitStatementSql(stmt);
        }

        /*
         * select [foo],[bar],[baz]
         * from [mytable] inner join [anothertable] on [mytable].[joinfield] = [anothertable].[joinfield]
         * where ([foo] = 1) and ([bar] > 2)
         * */
        static string SimpleSelect2()
        {
            Statement stmt = Statement.SimpleSel(
                new string[] { "foo", "bar", "baz" },                                                       // fields
                From.TableName("mytable").InnerJoin("anothertable", "joinfield", "joinfield"),              // from/join
                Where.Fld("foo").EQInt(1).And(Where.Fld("bar").GTInt(2)));                                  // where clause

            return SqlModel.emitStatementSql(stmt);
        }

        static string FullSelect()
        {
            Statement stmt = Statement.FullSel(
                new string[] { "foo", "bar", "baz" },                                                       // fields
                Quantifier.Top(10),                                                                         // quantifier
                From.TableName("mytable").InnerJoin("anothertable", "joinfield", "joinfield"),              // from/join
                Where.Fld("foo").EQInt(1).And(Where.Fld("bar").EQInt(2)),                                   // where clause
                new OrderBy[] { new OrderBy("foo", Order.Ascending) });                                     // order by

            return SqlModel.emitStatementSql(stmt);
        }

        static string GroupSelect()
        {
            Statement stmt = Statement.GroupSel(
                new string[] { "foo", "count(bar)", "max(baz)" },                                           // fields
                From.TableName("mytable").InnerJoin("anothertable", "joinfield", "joinfield"),              // from/join
                Where.Fld("foo").EQInt(1).And(Where.Fld("bar").EQInt(2)),                                   // where clause
                new string[] { "foo" },                                                                     // group by
                Where.All,                                                                                  // having
                new OrderBy[] { new OrderBy("foo", Order.Ascending) });                                     // order by

            return SqlModel.emitStatementSql(stmt);
        }

        static string HavingSelect()
        {
            Statement stmt = Statement.GroupSel(
                new string[] { "foo", "count(bar)", "max(baz)" },                           // fields
                From.TableName("mytable").InnerJoin("anothertable", "joinfield", "joinfield"),  // from/join
                Where.Fld("foo").EQInt(1).And(Where.Fld("bar").EQInt(2)),   // where clause
                new string[] { "foo" },                                                     // group by
                Where.Expr("count(bar)").GT(Val.IntLiteral(2)),                                   // having
                new OrderBy[] { new OrderBy("foo", Order.Ascending) });                     // order by

            return SqlModel.emitStatementSql(stmt);
        }

        static string BaseConstructorSelect()
        {
            Statement stmt = Statement.Select(
                new Field[] { Field.Name("foo"), Field.Name("bar"), Field.Name("baz") },    // fields
                Quantifier.Top(10),                                                         // quantifier
                From.TableName("mytable").InnerJoin("anothertable", "joinfield", "joinfield"),  // from/join
                Where.Fld("foo").EQInt(1).And(Where.Fld("bar").EQInt(2)),   // where clause
                new string[] { },                                                           // group by
                Where.All,                                                                  // having
                new OrderBy[] { new OrderBy("foo", Order.Ascending) });                     // order by

            return SqlModel.emitStatementSql(stmt);
        }

        static string AndReductionSelect()
        {
            Statement stmt = Statement.SimpleSel(
                new string[] { "foo", "bar", "baz" },                                       // fields
                From.TableName("mytable").InnerJoin("anothertable", "joinfield", "joinfield"),  // from/join
                Where.Fld("foo").EQInt(1).And(Where.All));                          // where clause

            return SqlModel.emitStatementSql(stmt);
        }

        static string OrReductionSelect()
        {
            Statement stmt = Statement.SimpleSel(
                new string[] { "foo", "bar", "baz" },                                       // fields
                From.TableName("mytable").InnerJoin("anothertable", "joinfield", "joinfield"),  // from/join
                Where.All.Or(Where.Fld("bar").EQInt(2)));                           // where clause

            return SqlModel.emitStatementSql(stmt);
        }
    }
}
